challenging query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

challenging query

Author

Topic

speedovic
Starting Member

4 Posts

Posted - 2012-10-25 : 12:30:42

I have the following table with 3 columns, this is just a sample (not a real one)
Number Decription Value
1 Green 100
1 Yellow 101
1 Blue 102
2 Chair 200
2 Table 101
2 Green 150
3 Car 200
3 plane 205
3 green 105

My first query is to find any record or row which contain value "101" No problemo in this scenario I find 2 records [1, Yellow,101] and [2, table, 101] and all the records with number 3 and the rest are ignored just perfect. Now I need to select other records based on the RESULT of the FIRST query, the number 1 and 2 are the true results. So from column [number] in this case I found [1 and 2], I want to search and add the value of any description = [green]. Still ignoring [3] which has NO [101] value.

The ideal result I want to display is
[1, yellow, 101] and Green is 100
[2, table, 101] and Green is 150

I have got a headache to get it work so far NO good result. If anyone has a any idea how to make the script for this case please let me know. I hope the questions is clear.

P.S the content of the table is fake just to get an impression what is about and fyi it's SQL + PHP.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-25 : 12:47:52

[code]
SELECT t.*,t1.Value AS GreenValue
FROM Table t
LEFT JOIN Table t1
ON t1.Number= t.Number
AND t1.Description = 'Green'
WHERE t.Value=101
[/code]

If you're guaranteed that all Numbers will have row with green you can replace LEFT with INNER in above statement

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kannan475
Starting Member

3 Posts

Posted - 2012-10-25 : 12:58:55

Select a.number, a.description, a.value, tablename.description, tablename.value from
(select number,description,val from tablename) as a, tablename where
a.number = tablename.number and tablename.description ='Green'

Should be good.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-25 : 15:02:04

quote:
Originally posted by Kannan475

Select a.number, a.description, a.value, tablename.description, tablename.value from
(select number,description,val from tablename) as a, tablename where
a.number = tablename.number and tablename.description ='Green'

Should be good.

Nope you missed the main filter

ie a.Value=101

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

speedovic
Starting Member

4 Posts

Posted - 2012-10-25 : 16:05:53

Thanks visakh16 and Kannan475 for your quick response.

Yes without the main filer it displays all records but when I add the main filter a.Value=101. it sows only one record ==> 2 Green 150 :(

when I try your solution I get the error (Table 'database.t1' doesn't exist) my table is called sample, and I think I am missing someting (I think I need more coffee)

quote:
Originally posted by visakh16

quote:
Originally posted by Kannan475

Select a.number, a.description, a.value, tablename.description, tablename.value from
(select number,description,val from tablename) as a, tablename where
a.number = tablename.number and tablename.description ='Green'

Should be good.

Nope you missed the main filter

ie a.Value=101

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-25 : 16:10:32

quote:
Originally posted by speedovic

Thanks visakh16 and Kannan475 for your quick response.

Yes without the main filer it displays all records but when I add the main filter a.Value=101. it sows only one record ==> 2 Green 150 :(

when I try your solution I get the error (Table 'database.t1' doesn't exist) my table is called sample, and I think I am missing someting (I think I need more coffee)

quote:
Originally posted by visakh16

quote:
Originally posted by Kannan475

Select a.number, a.description, a.value, tablename.description, tablename.value from
(select number,description,val from tablename) as a, tablename where
a.number = tablename.number and tablename.description ='Green'

Should be good.

Nope you missed the main filter

ie a.Value=101

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

thats not correct unless you've someother part whiich you've not shown us so far

can you post used query with some sample data from your tables?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

speedovic
Starting Member

4 Posts

Posted - 2012-10-25 : 16:43:02

quote:
Originally posted by visakh16

quote:
Originally posted by speedovic

Thanks visakh16 and Kannan475 for your quick response.

Yes without the main filer it displays all records but when I add the main filter a.Value=101. it sows only one record ==> 2 Green 150 :(

when I try your solution I get the error (Table 'database.t1' doesn't exist) my table is called sample, and I think I am missing someting (I think I need more coffee)

quote:
Originally posted by visakh16

quote:
Originally posted by Kannan475

Select a.number, a.description, a.value, tablename.description, tablename.value from
(select number,description,val from tablename) as a, tablename where
a.number = tablename.number and tablename.description ='Green'

Should be good.

Nope you missed the main filter

ie a.Value=101

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

thats not correct unless you've someother part whiich you've not shown us so far

can you post used query with some sample data from your tables?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

for testing I am really working on exactly the same database I mentioned with 4 fields a key field and the 3 I mentioned before, data is captured as it is on my 1st message, I am trying to keep it simple. This is my select statement no special codes

// Retrieve data
$data = mysql_query("SELECT sample.*, t1.Value AS GreenValue
FROM sample
LEFT JOIN t1
ON t1.Number = sample.Number
AND t1.Description = 'Green'
WHERE sample.Value=101")
or die(mysql_error());

$info = mysql_fetch_array( $data );

// print data
while($info = mysql_fetch_array( $data ))
{
Print... etc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-25 : 16:54:31

whats t1? it should be same table in fact


SELECT sample.*, t1.Value AS GreenValue
FROM sample
LEFT JOIN sample t1
ON t1.Number = sample.Number
AND t1.Description = 'Green'
WHERE sample.Value=101

also one thing i noticed is you're using mysql and not ms sql server. This is ms sql server forum so solution given here are guaranteed to work only in sql server. You may be better off posting this in some MySQL forum if provided solution doesnt work in MySQL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

speedovic
Starting Member

4 Posts

Posted - 2012-10-25 : 16:57:24

sorry ;)

Kannan475
Starting Member

3 Posts

Posted - 2012-10-26 : 09:31:37

quote:
Originally posted by visakh16

quote:
Originally posted by Kannan475

Select a.number, a.description, a.value, tablename.description, tablename.value from
(select number,description,val from tablename) as a, tablename where
a.number = tablename.number and tablename.description ='Green'

Should be good.

Nope you missed the main filter

ie a.Value=101

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks Visakh I did miss the main filter.
a.value =101

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-26 : 09:45:20

quote:
Originally posted by Kannan475

quote:
Originally posted by visakh16

quote:
Originally posted by Kannan475

Select a.number, a.description, a.value, tablename.description, tablename.value from
(select number,description,val from tablename) as a, tablename where
a.number = tablename.number and tablename.description ='Green'

Should be good.

Nope you missed the main filter

ie a.Value=101

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks Visakh I did miss the main filter.
a.value =101

thats ok

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources