Order By Based On Results From Another Table
I have a query that , and i need to order the result based on wheter or not the ID of my main table appears on another table, somewhat like this:
SELECT A.id AS Id, A.nome AS Nome
FROM main_table A
JOIN joined_table JT ON JT.id = A.id_cat
ORDER BY ("first whoever have a row in" another_table AT)
another_table stores the image paths related to main_table , i need to show all query results from main_table even if they don't have a image (row in another_table), but i need to show whoever have a image (a row in another_table) first...
I've tryied to join with another_table and even to use sobqueryes in ORDER B... but so far i got no results. it's even worst because this query is a monter with multiple lines (the sample is simplyfied...)
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
How To Delete From 1 Table Some Rows Based On Match Results In 2nd Table?
How to delete from TABLE-1 all rows with indexes "i" that match to index j=2 from TABLE-2? TABLE 1: +---+------+ | i | name | +---+------+ | 1 | item1 | | 1 | item2 | | 7 | item3 | <-- delete all rows with i=5,6,7 | 6 | item4 | <-- delete | 5 | item5 | <-- delete | 5 | item6 | <-- delete | 7 | item7 | <-- delete +---+------+ TABLE 2: +---+---+------+ | j | i | name | +---+---+------+ | 1 | 1 | item1 | | 1 | 3 | item2 | | 1 | 2 | item3 | | 2 | 5 | item4 | <---- j=2 => i=5 | 2 | 6 | item5 | <---- j=2 => i=6 | 2 | 7 | item6 | <---- j=2 => i=7 | 3 | 8 | item7 | +---+---+------+
View Replies !
View Related
How Do I Generate Results Based On Totals Of Another Table But For 1st Table?
This is what I want to do: 1- I have Two tables: polls_created and votes 2- Table polls_created is like: poll_id owner poll_subject 3- Table votes has the votes issued for a given poll, like this: vote_id poll_id vote vote_date So what I need to do is to look at these 2 Tables and generate results based on values of these 2 tables. How do I then generate this result: MySQL Code: SELECT poll_id, owner, poll_subject, COUNT(vote_id) AS number_of_votes FROM polls_created, votes "sorted by polls that have gotten most number of Votes" Of course "sorted by polls that have gotten most number of Votes" is not real MySQL
View Replies !
View Related
'order By Alternating Table' Results And 'begins With' Select
I am just starting to learn SQL and have been working on a couple of questions and yes this is homework related. I am having trouble with 3 questions. I have the basis for the answers but am stuck on a couple of points. I don't expect a direct answer but a push in the right direction would be helpful. Also, if anyone can recommend additional resources for assisting with learning SQL, that would be great. The first question is as follows: A company is preparing invitations to its annual holiday celebration and wants to invite its sales staff (everyone in the SALESREPS table) and its customers (everyone in the CUSTOMERS table). Write a SQL statement that returns output looking like the following (including the sort order): Code:
View Replies !
View Related
Grouping Results Based On Internal?
I have in a database which stores insurance quote requests. I want a query that counts the number of quotes by each referrer, grouping quotes made within a 10 minute period as 1 quote Here is an example of the table and the wanted results. TABLE ID referrer type time ip 1 XADW0124 MC 1161683434 82.7.164.59 2 XADW0124 MC 1161683464 82.7.164.59 3 XADW0124 MC 1161683484 82.7.164.59 4 XADW0124 PC 1161684425 62.173.110.62 5 XADW0139 MC 1161685322 195.137.84.105 RESULT ID referrer type time ip 3 XADW0124 MC 1161683484 82.7.164.59 4 XADW0124 PC 1161684425 62.173.110.62 5 XADW0139 MC 1161685322 195.137.84.105
View Replies !
View Related
Change Value In Field Based On Order
I have a field that is an INT and can contain a value of 0 or greater. When the value is 0 it creates a unique situation and users of the site will sometimes change this to 0 on purpose because this value can have your item show up in the search results on the first page. Why, if a person orders the information ASC, it will show up first. I have to allow this field to be 0, long story, but I want to "punish" them if this is so. Based on the type of search (ASC or DESC) as the user can pick. I want those items to always show up last. Currently I am passing a variable that gets set via a form w/ that information, but I don't know how to check in the query how the variable is set. I was thinking CASE but I thought that only looks at a field in the db, not some variable that is being passed. Either way, I hope this is making sense. Here is the query (pretty simple). Code: ......
View Replies !
View Related
Order Records Based On Two Columns
I'm working on PM feature for my site. So far I have this table: +-----------------+-----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+-----------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | subject | char(100) | YES | | NULL | | | body | blob | YES | | NULL | | | r_id | int(11) | YES | | NULL | | | s_id | int(11) | YES | | NULL | | | date_sent | int(11) | YES | | NULL | | | date_read | int(11) | YES | | 0 | | | r_date_to_trash | int(11) | YES | | NULL | | | s_date_to_trash | int(11) | YES | | NULL | | | r_status | int(11) | YES | | NULL | | | s_status | int(11) | YES | | NULL | | +-----------------+-----------+------+-----+---------+----------------+ r_id - "Receiver's ID" s_id - "Sender's ID" r_date_to_trash - "Unix timestamp when receiver deletes the message" s_date_to_trash - "Unix timestamp when sender deletes the messages" r_status - "receivers msg status, if r_status == 1 then message is in the trash s_status - "senders msg status, if s_status == 1 then message is in the trash So this is the query I have right now: SELECT * FROM pm WHERE (r_id = 1 AND r_status = 1) OR (s_id = 1 AND s_status = 1) This is the result: +-----+------+------+-----------------+-----------------+----------+----------+ | ID | r_id | s_id | r_date_to_trash | s_date_to_trash | r_status | s_status | +-----+------+------+-----------------+-----------------+----------+----------+ | 104 | 1 | 2 | 1226972079 | NULL | 1 | 0 | | 110 | 1 | 2 | 1226971762 | NULL | 1 | 0 | | 111 | 1 | 2 | 1226971891 | NULL | 1 | 0 | | 112 | 1 | 2 | 1226972033 | NULL | 1 | 0 | | 113 | 1 | 2 | 1226972462 | NULL | 1 | 0 | | 120 | 2 | 1 | 0 | 1227103836 | 0 | 1 | | 122 | 2 | 1 | 0 | 1227103841 | 0 | 1 | | 127 | 2 | 1 | 1226972346 | 1226972090 | 1 | 1 | | 128 | 2 | 1 | 0 | 1226972095 | 0 | 1 | | 129 | 2 | 1 | 0 | 1226972176 | 0 | 1 | | 130 | 2 | 1 | 0 | 1226972396 | 0 | 1 | +-----+------+------+-----------------+-----------------+----------+----------+ This is the problem: I'm trying to list the messages that are in the trash in order by date they were deleted (r_date_to_trash, s_date_to_trash). I need to join these two fields somehow together and then I could just do ORDER BY whatever DESC. That way the messages in the trash will show up in the order they are deleted.
View Replies !
View Related
Order These Results
Lets say i have the following table (1 field): 1 1 1 2 3 3 3 3 4 4 So there's 3 1's 1 2's 4 3'd and 2 4's I want it to display the items like this 3 14 2 (the one with the most elements on top and so on) Is it possible to do it in a query or do i need some code to count one by one ?
View Replies !
View Related
Can I Specify Order Of Results [was: Is This Possible?]
I am trying to query a table with real estate listings. I have variables company, price, bedrooms. I want to do a search of all listings between x and y price, and between b and c amount of bedrooms. I can do all that stuff just fine. However, I want the query to return listings from MY company first. So basically a query there company=mine and then goes through the search, and then company=any and goes through again. Any idea how to do this or what its' called?
View Replies !
View Related
Order Query Results
Regarding a dummy set of data below, ID Status 1 Open 2 Closed 3 Expired 4 Closed 5 Open 6 Cancelled 7 Expired 8 Cancelled I want to view the results but order them according to the status. i.e. i want to view in order of Open, Closed, Expired, Cancelled I can only think of a long drawn out way by which i would create the four seperate queries.
View Replies !
View Related
ORDER BY With Negative Results
i have some problem with ORDER BY with negative results when i order by some fields that the fields which has negative results should be come first but it is not coming right for example results for field `general total` a = 0 b = -10 c = 0 d = 20 e = 30 when i order the statement according to field ` general total` it should come like b a c d e but it is coming a b c d e where i am doing wrong? what should i do?
View Replies !
View Related
Ordering Results Using Custom Order.. Eh...
bad topic title, sorry. I don't think this is possible, but it would be cool if it is somehow. Let say I have the following query: SELECT some_field, some_field2 FROM some_table WHERE some_field IN(5,4,8,2); As I understand it, as soon as MySQL finds a row matching the IN statement, it'll be 'added' to the record set. Is there any way to have MySQL return the records ordered by the IN clause? Something like: some_field some_field2 ---------- -------------- 5 blah 4 woo 8 eh? 2 woo 2 Instead of the following, which represents the regular sorting of the table's data: some_field some_field2 ---------- -------------- 2 woo 2 4 woo 5 blah 8 eh? I can rearrange the result set using PHP, but I'd love to know if MySQL offers something for this
View Replies !
View Related
Reversing Query Results (not The Same As ORDER BY)
I am trying to reverse the order in which the results of my query are given. I am tweaking a gallery. I need to get the previous 3 pics based on the pic I am looking at. The following code is a simplified version of what I have. The problem is, when I echo the results, the thumbnails are displayed in DESC order (i need them to display in ASC order) but if I order the query using ASC, the query gets the wrong images. SELECT pic_id WHERE pic_id < $current_pic_id ORDER BY pic_id DESC LIMIT 3 I need the results to show like this: oldest pic | older pic | old pic | current pic | new pic | newer pic | newest pic I've got the new pics sorted out but the old pics are causing a little problem... So basically, is there a way that I can reverse the results of my query?
View Replies !
View Related
Importing A Csv File Results In Wrong Order
I have a table with some records on it. I am using phpmyadmin to import some extra record from a csv file. The import looks good but the new records are imported in wrong order as the original file; mainly backwards. I have a key field and in the file the order is as expected 1, 2, 3, etc. but when I query the table, the order displayed is 3, 2, 1.
View Replies !
View Related
Speed Up Order Table Table-name Order By Col-name For Large Table?
I have a table with 2 column: id and name. Column name is of type varchar(100). The table has about 0.1 billion rows, taking 11G disk space. Now what I want to do is alter the table by the name column. I ran the command: "order table table-name order by name" 1).Firstly, it create tmp table,which takes 19G disk space. 2).After that,the 19G space seems to be released(I see the free disk space increase by "df"). 3).Then I see that in the mysql directory, the size of the mysql file #sql-14f7-2.MYD" increases 2M every time, but very slow. I guess this mysql file is the new table after order. But how can it be so slow? It only increase 2M every 2 minutes or so, which would takes weeks to finish the command. So could any one tell me how can I speed up this command? Are there any variables I can adjust to make this faster?
View Replies !
View Related
Updating Rows In Table B Based On Related Field In Table A
Ver 4.1.8-standard for apple-darwin7.6.0 on powerpc (Official MySQL-standard binary) I am trying to do some data migration based. I have several tables that contain our legacy pkey field and I want to update the tables with new ID's. I need to do this several times and have tried it several ways to no avail. Table A --------- companyID int(10) pKey legacyID int(10) old legacy pkey Table B --------- bAID int(10) pkey companyID int(10) legacyID int(10) Table A has values for both companyID (unique key) and legacyID. Table B has values for bAID (unique key) and legacyID but companyID is empty. I need to update tableB.companyID with tableA.companyID based on tableb.cSerialID to tablea.cSerialID relationship. I need a query that will update ALL rows.
View Replies !
View Related
Updating Table Based Upon Matching Field In Second Table
I have a database of books that was originally created as a flat file. Each record has a number of fields, including the authors name. I'm trying to convert the database to something a little more efficient. I've created a new table (called Authors) of unique authors names and assigned each one a unique ID. I've added a new field in the original table (called Books) for the author's ID. Now, I need to update the original table with the author ID from the Author's table. Something like this: UPDATE Books SET AuthorID = Authors.AuthorID WHERE AuthorName = Authors.AuthorName This obviously doesn't work. Any assistance on how to forumulate this query (or, if I'm headed down the wrong path, the correct way to do this operation) greatly appreciated.
View Replies !
View Related
Select Data From 1 Table Based On Criteria From Another Table
is it possible to select all the data in one table based on a criteria from another table? for instance i want to select all the therapist from massage_therapist WHERE massage_schedule.finish > 0. i don't want merged results. i just need to list all therapist based on the where criteria from a different table. these two tables have the therapist_id in common.
View Replies !
View Related
Update Table Based On Email In Another Table
I'm having trouble updating the entries from a table. The situation is as follows: Customer table contains: 1) customer_email_address 2) customer_newsletter (value 0 or 1) Visitor table contains: 1) email The visitor table contains email addresses from customers that have signed up through another system. I would like to update the customer table and set customer_newsletter to 1 where customer_email_address matches email from the visitor table.
View Replies !
View Related
Re-order The Natural Order Of All Of All The Rows Of A Table
Basically I want to re-order the rows of my entire database. It has many thousands of rows, and people are constantly running the same query against it. The problem is its quite slow (using a shared host). I know you can use an sql query to order by column, however I need to re-order twice and it really seems to be slow due to this. Since the query is always the same, if the rows were already ordered in the database , then the ordering query wouldn’t need to be done. Is there an easy way to do this, without deleting each and every row, then inserting them again? basically in the database I have: The natural order when you do a select * from this table (without using an order by query): ________________________ |cola |col.. |coln | |dataA |dataB |dataC | |dataX |dataY |dataZ | |datal |datam |datan | (obviously a lot bigger than this) and I want reorder the entire table , eg The natural order when you do a select * from this table: ________________________ |cola |col.. |coln | |dataA |dataB |dataC | |datal |datam |datan | |dataX |dataY |dataZ | So is there an easy way to do this, am I just being dumb? or do I need to delete the entire table and insert the rows one at a time in order?
View Replies !
View Related
Multiple ORDER & SUB ORDER On The Same MySQL Table
I am trying to do multiple ORDERS or SUB ORDERS on the same MySQL table, and I'm loosing my mind trying to fathom the logic and SQL statement to use, I'm no MySQL genius! more a newbie. *** SEE ATTACHED IMAGE PLEASE I have tried all sorts of SQL statements, e.g: SELECT * FROM categories GROUP BY parent ORDER BY order, parent DESC Nothing seems to work. I think my only solution is to do a bubble-sort after putting the whole table in a PHP ARRAY ? I'd also like the menu to act like the + pop-open sub-menu boxes on the Forum left column menu.
View Replies !
View Related
UPDATE Based On Value In Another Table
How do I update a table to remove orphaned references to a second table? I've deleted rows in the second table, which has a unique auto_increment key. The first table now has references to keys that no longer exist. I need to update the first table, setting the value to NULL where the referenced key no longer exists. Something like: UPDATE table1 SET table2ID = NULL WHERE table1.table2ID NOT FOUND IN table2.ID; The NOT FOUND IN isn't SQL, of course, but I'm not sure what should go there.
View Replies !
View Related
Update Based On Another Table
Is it possible to do an update in MYSQL based on another table? I have version 3.23 and when I try to run this statement: UPDATE ApplicationTbl INNER JOIN AcademicTbl ON ApplicationTbl.CampusID = AcademicTbl.CampusID AND ApplicationTbl.Application_Period = AcademicTbl.Application_Period SET ApplicationTbl.App_Status = 'Qualified' WHERE AcademicTbl.Sem_OnCampus >= '1' AND AcademicTbl.GPA >= '2.4' AND AcademicTbl.Judicial_Sanction IS NULL It keeps saying its wrong. Even though I know its not
View Replies !
View Related
Selecting From One Table Based On Another?
I have a table for products and another table for the type of product it is like this: I need this is be in a seperate table because one product can be several different types like ring,band,wedding,three-stone,etc. but how can I then select a product based on criteria from the types table say I wanted to select all products that are type = "Band" and type != "Fancy" ?
View Replies !
View Related
Table Based On Info In Another Table
Essentially this is what i want to do. I have two Tables, Table A- has the field "id" which is the primary key Table B- has the field "id" which is the primary key Select * from tableA where Table A.id is not in tableB.id How can i form a statement that will accomplish the above.
View Replies !
View Related
Updating A Table Based On The Value In Another Table In Ver 3.23.54-Max
I am trying to update fields in table SOLAR_SYSTEM based on the values in RESEARCH_LEVEL table This is a SQL that I am trying to use UPDATE SOLAR_SYSTEM JOIN RESEARCH_LEVEL ON SOLAR_SYSTEM.USER_ID=RESEARCH_LEVEL.USER_ID SET SOLAR_SYSTEM.CASH_METAL=SOLAR_SYSTEM.CASH_METAL+1000, SOLAR_SYSTEM.CASH_CRISTAL=SOLAR_SYSTEM.CASH_CRISTAL+1000, SOLAR_SYSTEM.CASH_ENERGY=SOLAR_SYSTEM.CASH_ENERGY+1000 WHERE RESEARCH_LEVEL.TYPE='M' AND RESEARCH_LEVEL.LEVEL=1; I got error "ERROR 1064: You have an error in your SQL syntax near 'JOIN RESEARCH_LEVEL ON SO LAR_SYSTEM.USER_ID=RESEARCH_LEVEL.USER_ID SET SOLAR_SY' at line 1" I don't know what is wrong. Can I do such an update in Ver 3.23.54-Max in the first place ? IF yes, then what's wrong with my statement?
View Replies !
View Related
Creating New Table Based On Old Table
I have a MySQL database with a user table will lots of DUPLICATE email. I want to delete the rows completely having the duplicate email. I found a way out is to use some thing like this: Code: insert into newtable (email) select distinct email from oldtable But this way, only the email column is populated in the new table. I want all the other columns as well.
View Replies !
View Related
Join Table Based On Specified Condition
Is it possible to join two tables based on the specified condition in MySQL. For example, i have two tables t1,t2 and t3. The table t1 and t2 has field called name and the table t3 has field name class. I want to select the names from t1 or t2 based on the condition of class(t3). If class=4 then select name from t1, if class=10 then select name from t2.tell the query in mysql.
View Replies !
View Related
Deleting From A Table Based On Record Id
I need to execute the following query: delete from history where id=(select min(id) from history where user = username); this query doesn't work and gives me an error "ERROR 1093 (HY000): You can't specify target table 'history' for update in FROM clause" How do I rewrite this query? Ofcourse I can go for two queries, but I'd prefer to do it with a single query.
View Replies !
View Related
List Available Items Based On Prerequisite Table
I'm trying to develop such a scheme: TABLE Prerequisites PrerequsiteID (FK to Upgrades table; states a prerequisite for UpgradeID in the other column) UpgradeID (FK to Upgrades table as well) TABLE BuildingUpgrades BuildingID (FK to Buildings table) UpgradeID (FK to Upgrades table; states what Upgrade Building already has) So it's a Building -> Building_Has_Upgrades -> Upgrades -> Upgrades_Have_Prerequisites thing (Building has 1..n Upgrades, Upgrade has 1..n prerequisites). In order for Building B to have an Upgrade U, that Building B has to have all the Upgrades which are required to have Upgrade U. What I want to do is to list all buildings with all upgrades available for them. I've been trying for almost whole day to solve this issue with different inner, left and right joins, subqueries and what not but I can't really do it in a clean way. The only working thing is a query, which I am ashamed of because it seems like a real waste of CPU time to me: SELECT BUPG.BuildingID, PRE.UpgradeID, COUNT(PRE.PrerequisiteID) FROM BuildingUpgrades BUPG JOIN Prerequisites PRE ON BUPG.UpgradeID = PRE.PrerequisiteID GROUP BY BUPG.BuildingID, BUPG.UpgradeID HAVING COUNT(PRE.PrerequisiteID) = ( SELECT COUNT(PrerequisiteID) FROM Prerequisites WHERE UpgradeID = PRE.UpgradeID GROUP BY UpgradeID ) What it basically does is takes all the available Upgrades, counts prerequisites that have been met by the building and checks if it's equal to the number of all prerequisites for that Upgrade. And it works but I still think there is a much better way to do it.
View Replies !
View Related
Table Creation Based On User Login
I'm trying to build a website that will allow the addition of users (w/ username, password, etc.). That seems simple enough. But is it possible to set something up (in PHP, for example) that will create a new table in the database for each new user? Ex. John Smith signs up, is verified, and a PHP script (or Java, or whatever) creates a table specifically for him. I know I'll need a master table of usernames, passwords, etc. These new tables would store certain user-specific data, such as preferences and other data the user might want to restrict access to. I know I could store all of this information in a master table, but I'm trying to think ahead. Worst case scenario, if there are millions of users (unlikely, but not impossible), I'd hate to have all that data in one table, but if I have to, I can.
View Replies !
View Related
Counting Levels On Adjacency Based Table
Does anyone know a single query that could successfully count the number of levels in a table based on the adjacency model? By "number of levels", I mean the number of LEFT OUTER JOIN that needs to be used in a query such like this one : SELECT level0.cat_ID AS level0_ID, level0.cat_parent_ID AS level0_parent_ID, level0.cat_name AS level0_name, level1.cat_ID AS level1_ID, level1.cat_parent_ID AS level1_parent_ID, level1.cat_name AS level1_name, level2.cat_ID AS level2_ID, level2.cat_parent_ID AS level2_parent_ID, level2.cat_name AS level2_name, level3.cat_ID AS level3_ID, level3.cat_parent_ID AS level3_parent_ID, level3.cat_name AS level3_name FROM ecom_categories AS level0 LEFT OUTER JOIN ecom_categories AS level1 ON level1.cat_parent_ID = level0.cat_ID LEFT OUTER JOIN ecom_categories AS level2 ON level2.cat_parent_ID = level1.cat_ID LEFT OUTER JOIN ecom_categories AS level3 ON level3.cat_parent_ID = level2.cat_ID WHERE level0.cat_parent_ID IS NULL ORDER BY level0_name, level1_name, level2_name, level3_name
View Replies !
View Related
Detecting Duplicates Based On One Field In A Table?
I can't seem to find an answer anywhere for this as most people want to delete duplicate records. I have a table, lots of fields, one of which is "title". I want mysql to return all the rows where "title" is the same. For instance if "title" was "Lord of the Rings" on id = 1 and id = 2, then the query would return both of the records, but ignore the others. It doesn't matter what the other field values are. I have found some SELECT COUNT( * ) AS num_entries, title FROM title_table WHERE title >1 GROUP BY title But this doesn't seem to work, although looks like it's on the right sort of lines? I'm not a Mysql expert, so my experience is mostly limited to the usual select, insert, update and delete queries!
View Replies !
View Related
Select Based On Two Rows In Mapping Table
I have a many to many relationship between a resource and tag: resource - 1:M - tagged_resource - M:1 - tag So a resource can have many tags and a tag can belong to many resources. The data would look like this: RESOURCE TABLE id: 1 name: resource1 TAG TABLE id: 1 tag: new id: 2 tag: tested TAGGED_RESOURCE resourceID: 1 tagID: 1 resourceID: 1 tagID: 2 How can I select the single row from the RESOURCE TABLE which has the tags 'new' and 'tested' in the TAGGED_RESOURCE TABLE? I have tried using 'IN' but it acts like an OR operator rather than AND.
View Replies !
View Related
Update Column Data Based On Another Table
I've got 2 tables: 'city' that list over 2000000 rows and 'profile' where each row are associated to a city. What I want to do is to update cities popularities based on profile without having to scan the hole 2000000 rows of 'city'. So is it possible to make those 2 query in one so I do not need to do a php loop: SELECT city, COUNT(city) FROM profile GROUP BY city; then UPDATE city SET popularity = COUNT(city) WHERE city.city_id = profile.city
View Replies !
View Related
Multiple Table Search Based On User Input
I'm working on a database containing our research data. I know very little about PHP, MYSQL, or database structure (except keep like data together in different tables and have a key field in each). I'm currently working with 3 tables (tbl_2006 and tbl_2007 structures are identical): ....
View Replies !
View Related
Populating Matrix Table Based On Books Taken By User
I've been trying to do this for 2 hours now. My noobness in mysql is not letting me get through =( USERS: id_user | f_name | l_name 1 | mike | smith 2 | david | bush etc... BOOKS: id_book | s_name | l_name 10 | book 1 | walking together 20 | book 2 | straight path 21 | book 2A| crooked path etc.... REGISTRY: id_fk_user | id_fk_book 1 | 10 1 | 21 2 | 10 2 | 20 2 | 21 etc.... Now, I am trying to fill out a table by row using pear's HTML Table that would end up like this: Name | ID | Book1 | Book2 | Book 2A | etc.. mike smith | 1 | x | | x | etc.. david bush | 2 | x | x | x | etc.. I am having a hard time because the table wants each row's data separetly before it send it to HTML output. And my SQL statement looks like this: SELECT id_user, f_name, l_name, id_book FROM USERS, BOOKS, REGISTRY WHERE id_user = id_fk_user AND id_book = id_fk_book which gets me: 1 mike smith 10 1 mike smith 21 2 david bush 10 2 david bush 20 2 david bush 21
View Replies !
View Related
Determining Which Table To Query Based On Data Within Tables
I have 2 tables: default_categories column 1: category_id column 2: category_name column 3: category_parent custom_categories column 1: custom_cat_id column 2: custom_cat_name column 3: custom_cat_parent The custom_categories table won't necessarily have anything in it but if it does, I need to choose the data from the custom_categories table over the data from the default_categories table. So if the default category has 3 rows with IDs | names: 123 | Dogs 456 | Cats 789 | Fish And the custom category has 1 row with IDs | names: 456 | Very Cute Cats I want my query of these 2 tables to produce the following IDs | names: 123 | Dogs 456 | Very Cute Cats 789 | Fish I've tried joins like the one below but they aren't working because if there is no custom_cat_id, it won't give me the result for the default category_id. MySQL SELECT * FROM default_categories LEFT JOIN custom_categories ON category_id = custom_cat_id WHERE category_parent = '' AND custom_cat_parent = '' ORDER BY $order_by $sort
View Replies !
View Related
|
TRACKER
