I need perform the following:

I have two tables. I need to select a piece of data from table one and use that as an input to table two...

Example:

SELECT * FROM vehicles WHERE type=(SELECT type FROM chevrolet WHERE chassy='truck')

It's returning the error "subquery returns more than one row"

I know it does. I want it to. What I need is a comprehensive list from list 1 where the information in subquery 2 matches.

Convoluted scenario, but it makes more sense than my actual application.

8/17/2007 2:39:52 AM

message_topic.aspx?topic=484972

Quote :

"Convoluted"

do a google search on joins. i'm sure you'll find plenty of information on how to do this

8/17/2007 3:00:09 AM

haha, joins are convoluted

[Edited on August 17, 2007 at 3:32 AM. Reason : why is chevrolet a table?]

8/17/2007 3:31:13 AM

SELECT * FROM vehicles WHERE type IN
(SELECT type FROM chevrolet WHERE chassy='truck')

the reason its saying "returned more than one row is because you used =, instead of IN, and there are many different "types" of "trucks" that Chevrolet makes, and the main query doesn't know which type to compare it against.

fyi, if you want to get pretty good at sql with minimal effort, check out this site:http://sqlzoo.net/

8/17/2007 7:32:15 AM

Something tells me you'll need to throw in the DISTINCT keyword there.

8/17/2007 7:35:23 AM

SELECT * FROM vehicles LEFT JOIN chevrolet ON vehicles.type=chevrolet.type WHERE chevrolet.chassy='truck'

that'll give you all the data from both tables, if you just need the vehicle table info, go with synapse's subquery

8/17/2007 8:38:46 AM

Quote :

"haha, joins are convoluted"

I said the example was convoluted. Not joins.

Thanks synapse, that worked.

8/17/2007 10:39:47 AM

the only thing convoluted about the example is the fact that you have a vehicles table and a chevrolet table. i doubt the structures of the two are so different that you can't just use one table. otherwise, it's a very basic and simple scenario

8/17/2007 10:46:11 AM

the example was made up. If I gave you my real scenario, folks would be lost in terminology.

8/17/2007 11:18:44 AM

^ yeah thats the same thing that happens to me when I need to ask SQL questions.

8/17/2007 11:19:19 AM

gotchya

8/17/2007 11:21:07 AM

i didn't know that whole IN keyword existed

the extent of my sql has been simple selects, inserts, and updates...

8/17/2007 12:38:01 PM

btw, it's spelled chassis, and not chassy.

8/19/2007 11:40:59 AM

personally I would have gone with BigMan157's suggestion, however its really programmer preference. I learned a lot of my first SQL with MySQL 3.x, which was before it supported subselects. Anything you can express with subselects, you can express with joins. Leave it up to the DBAs to optimize later

8/19/2007 11:43:00 PM

is there anyway to put a variable as a column name in the where clause... like

SELECT courseNumber
FROM training
WHERE [variable1] = [variable2]

..variable1 is coming from a selection in a list of employees and will be their job title, variable2 is the level of training selected (a b or c) and the training table has column headers of employee titles and fields or a b or c for different courses required.

If I put in WHERE CIS = 'a' it works fine, returns the CIS training courses required for training level 'a'. But, can't get it to work this way.. I really probably need to re-do the training table because somebody else wrote it that was clueless on how the rest was going to work..but that's what I have to work with right now.

Any better ways of doing it would be appreciated.

9/10/2007 12:22:23 PM

^ i'm not really sure what you're asking...that post doesn't make a whole lot of sense. but have you ever used the LIKE operator? look 1/2 the way down this page or so and look at all the comparison operators.

http://www.firstsql.com/tutor2.htm

also check this site...great place to learn SQL
http://sqlzoo.net/

9/10/2007 1:20:01 PM

yeah, nevermind that idea.. doing it a different way. I'll have another, more understandable question in a few

9/10/2007 1:21:16 PM

haha so i guess i thought el nacho was a chick, and I was about to be amazed that she (*he*) was the first to notice chassy vs. chassis.

but nvm.

thought i would share, tww

9/10/2007 10:19:55 PM