SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

Azzmodan: Mar 13, 2004

Xae posted:

code:
SELECT * FROM entries 
WHERE ID = (SELECT entryID FROM metadata 
                        WHERE fieldID = (SELECT ID FROM metafields WHERE name=:name))
Isn't that the same as
code:
SELECT	*
FROM	entries
    INNER JOIN metadata ON metadata.entryID = entries.ID
    INNER JOIN metafields ON metafields.ID = metadata.fieldID
WHERE	metafields.name = :name
It seems like you are using sub-queries instead of just doing a join, unless my still hung-over rear end is missing something.

No, the first one only grabs the data from entries where the id matches that what is searched.

The second joins the entries, metadata, metafields tables into a single set (that we will now work on), and then grabs from that only the 'set'(that includes metadata/metafields in addition to the entries fields) that is searched.

if you had done "SELECT entries.*" in the second you would have gotten the same result as from the first, but there's still a difference in joining tables and filtering from that then doing lookups on an id.

I use the first if I just need data from the first table and the others are mere lookups(like in this example) and I use joins when I need data from both tables or are working on master/detail type tables.

I do dislike plural names on my tables though, if each row contains only a single entry it should be called entry, and if it contains more then one you need to rethink the whole thing

# ¿ Jan 2, 2008 13:55

Adbot: ADBOT LOVES YOU

# ¿ May 3, 2024 16:53

Azzmodan: Mar 13, 2004

Freezy posted:

I have a string "$string" that will contain data in the same format as the above. I must discover if any line of $string exists in any row, for the column in question. I also must know which line of $string matched successfully.

code:

SELECT myfield, COUNT(*) AS Recordcount 
FROM mytable 
WHERE myfield IN ('Orange', 'Tomato') 
GROUP BY myfield

That should give you every string that matched, with the number of matches

# ¿ Jan 3, 2008 11:02

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'