SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 challenging query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

speedovic
Starting Member

4 Posts

Posted - 10/25/2012 :  12:30:42  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 10/25/2012 :  12:47:52  Show Profile  Reply with Quote

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


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/

Go to Top of Page

Kannan475
Starting Member

3 Posts

Posted - 10/25/2012 :  12:58:55  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/25/2012 :  15:02:04  Show Profile  Reply with Quote
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/

Go to Top of Page

speedovic
Starting Member

4 Posts

Posted - 10/25/2012 :  16:05:53  Show Profile  Reply with Quote
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/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/25/2012 :  16:10:32  Show Profile  Reply with Quote
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/

Go to Top of Page

speedovic
Starting Member

4 Posts

Posted - 10/25/2012 :  16:43:02  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/25/2012 :  16:54:31  Show Profile  Reply with Quote
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/

Go to Top of Page

speedovic
Starting Member

4 Posts

Posted - 10/25/2012 :  16:57:24  Show Profile  Reply with Quote
sorry ;)
Go to Top of Page

Kannan475
Starting Member

3 Posts

Posted - 10/26/2012 :  09:31:37  Show Profile  Reply with Quote
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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/26/2012 :  09:45:20  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000