The Old Joel on Software Forum: Part 4 (of 5)

mysql query

howdo, been helped here before with some amazing pseodo-code.. so im back..
I can do this using code but need to know how to do it using sql for mysql.

I have 2 tables,

user:

id uid
-------
1 abc
1 zxc
1 qwe
2 vbn

lead:

id2 info
---------
abc info
abc info1
zxc info2
qwe info3
vbn info4

I need to: Select * from lead where all id are 1 in the user table.

So I could do for example something like:

multiVar= 'Select uid from user where id=1;'

then

Select * from lead where id2=multiVar;

pushing the multiple values from the first select into a second sql query.

Im sure there is a way of doing this using: lead.id2 and user.id - '.' stuff.. but my sql isnt that advanced yet.

Anyone feel like helping me out here, so I can understand how this would be done in sql rather than code?

thanks in advance.. :)

aku beg
Wednesday, April 7, 2004

It's called a JOIN. Go to google and search for SQL Join tutorials; it'll go over the concept of a JOIN and then explain the syntax in good detail.

MR
Wednesday, April 7, 2004

select * from lead where lead.id2 in (select uid from user where id = 1)

Discalimers: my SQL is rusty; and I don't know whether mysql supports this feature of sql.

Christopher Wells
Wednesday, April 7, 2004

You need to do a basic inner join:

SELECT lead.*
FROM user INNER JOIN lead ON uid=id2
WHERE id=1

(Or if you must use mysql, this might work:

SELECT lead.*
FROM user, lead
WHERE user.uid = lead.id2
AND user.id=1
)

Greg
Wednesday, April 7, 2004

You should do everything in one query.

select id2, info
from lead, user
where
info=uid and id=1

-tim

a2800276
Wednesday, April 7, 2004

thanks greg.. the second one worked I think..
seems all so easy now that ive been shown,

I learn much better from example than from documentation.

aku beg
Wednesday, April 7, 2004

Oooh, that's very basic skill that you should learn in the first 5 minutes of learning SQL.

Richard Sunarto Yu
Wednesday, April 7, 2004

"aku beg", how do you get your nickname? May I guess that you are Indonesian?

note: "aku" --> I
therefore "aku beg" --> "I beg"

I may be wrong for this guessing :-)

Richard Sunarto Yu
Wednesday, April 7, 2004

Look at:

"A Gentle Introduction to SQL" - http://sqlzoo.net/
Has sections for various databases. Haven't used it personally, but looks decent and gets recommended on several forums when someone asks about SQL tutorials

"SQL for Web Nerds" - http://philip.greenspun.com/sql/
Based on Oracle, so the syntax is different in some areas (like outer joins) - but a lot of the basics are the same. I like his writing style and it's what I use when I need a refresher on parts of SQL I haven't used recently.

RocketJeff
Wednesday, April 7, 2004

http://www.w3schools.com/sql/ is a good site for learning SQL, but the SQL (from what I can tell) is MS-SQL biased. GOOD site, but double-check with http://www.mysql.com/ on the details.

Andrew Burton
Wednesday, April 7, 2004

Subselects are probably the most inefficent method.

Anyone know if there is likely to be a performace difference between the explicit join and the implied join?

Or would the query optimizer reduce them to the same thing?

Eric Debois
Wednesday, April 7, 2004