• 0

SQL query - need some help


Question

Hi guys. I need some help with a damn sql query that I can't seem to make it work. It should be easy, but I just can't figure out what I'm doing wrong. 

So, I have a table with a few columns that is editable by users on the front-end. Each user sees only his entries. The first column is the user's ID (I'm on Wordpress, by the way). Next, I'm trying to do a query that will pull all the data submitted by users and output it into a "public" table. Everything is fine, except that I want the table to show the user's name instead of the ID. I've read a little and it seems I can do this with a INNER JOIN on that query, but I can't get it to work, I constantly get sql statement errors when trying to save the damn thing. Somehow I need to link the user_nicename column from wp_users to the ID column and output the name instead of the id.

 

Any help? Thank you!

 

P.S. Here's how my query looks like, and it doesn't work (the JOIN part):

 

SELECT wp_table_1.userID,
       wp_table_1.date,
       wp_table_1.name,
       wp_table_1.input1,
       wp_table_1.input2,
FROM wp_table_1
SELECT wp_users.user_nicename, wp_table_1.name
FROM wp_users
JOIN wp_table_1
ON wp_users.ID=wp_table_1.name

 

Link to comment
https://www.neowin.net/forum/topic/1317318-sql-query-need-some-help/
Share on other sites

7 answers to this question

Recommended Posts

  • 0
  On 24/12/2016 at 00:27, TDT said:

Hi guys. I need some help with a damn sql query that I can't seem to make it work. It should be easy, but I just can't figure out what I'm doing wrong. 

So, I have a table with a few columns that is editable by users on the front-end. Each user sees only his entries. The first column is the user's ID (I'm on Wordpress, by the way). Next, I'm trying to do a query that will pull all the data submitted by users and output it into a "public" table. Everything is fine, except that I want the table to show the user's name instead of the ID. I've read a little and it seems I can do this with a INNER JOIN on that query, but I can't get it to work, I constantly get sql statement errors when trying to save the damn thing. Somehow I need to link the user_nicename column from wp_users to the ID column and output the name instead of the id.

 

Any help? Thank you!

 

P.S. Here's how my query looks like, and it doesn't work (the JOIN part):

 

SELECT wp_table_1.userID,
       wp_table_1.date,
       wp_table_1.name,
       wp_table_1.input1,
       wp_table_1.input2,
FROM wp_table_1
SELECT wp_users.user_nicename, wp_table_1.name
FROM wp_users
JOIN wp_table_1
ON wp_users.ID=wp_table_1.name

 

Expand  

I can't imagine that join working unless the database is really that poorly designed. Are those two columns even the same data types?

  • 0
  On 24/12/2016 at 00:41, adrynalyne said:

I can't imagine that join working unless the database is really that poorly designed. Are those two columns even the same data types?

Expand  

What do you mean? It's a basic table, with a few columns. As for the Wordpress users table, how would I know if it's poorly designed? :) About the data type, not realy. The one in my table is automatic, it just adds the user ID (each user that submits an entry). The Wordpress one is the user's name. Should I create another column in my table and join that one with the one in wp_users?

  • 0
  On 24/12/2016 at 01:04, TDT said:

What do you mean? It's a basic table, with a few columns. As for the Wordpress users table, how would I know if it's poorly designed? :) About the data type, not realy. The one in my table is automatic, it just adds the user ID (each user that submits an entry). The Wordpress one is the user's name. Should I create another column in my table and join that one with the one in wp_users?

Expand  

You should be joining the tables on a column that they both share, preferably with one them being a primary key and the other a foreign key. They don't need to be named the same but good design suggests they should at least be apparent that they are related. 

  • 0
  On 24/12/2016 at 01:15, adrynalyne said:

You should be joining the tables on a column that they both share, preferably with one them being a primary key and the other a foreign key. They don't need to be named the same but good design suggests they should at least be apparent that they are related. 

Expand  

But there is one that both tables share, the ID table. My output shows the correct id with its own entries, but how on earth do I replace that ID with the user's name? 

  • 0

Id think it would be something more like

 

SELECT wp_table_1.userID,
       wp_table_1.date,
       wp_table_1.name,
       wp_table_1.input1,
       wp_table_1.input2,
       wp_users.user_nicename
FROM wp_table_1
JOIN wp_table_1
ON wp_users.ID=wp_table_1.userID

 

  • 0

you need to have a matching UserID (key-int) column in your users table, along with a matching column in your data table... and the join must be on that field.

 

SELECT table2.UserName,
       table1.date,
       table1.input1,
       table1.input2,
FROM table1
INNER JOIN table2
ON Table1.UserID = Table2.UserID
Where Table2.UserName like 'Value'

 

 

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • Download this SQL Essentials For Dummies eBook (worth $10) for free by Steven Parker FOR DUMMIES is a trademark of John Wiley & Sons, Inc. A right-to-the-point guide on all the key topics of SQL programming SQL Essentials For Dummies is your quick reference to all the core concepts of SQL—a valuable common standard language used in relational databases. This useful guide is straightforward—with no excess review, wordy explanations, or fluff—so you get what you need, fast. Great for a brush-up on the basics or as an everyday desk reference, this book is one you can rely on. Strengthen your understanding of the basics of SQL Review what you've already learned or pick up key skills Use SQL to create, manipulate, and control relational databases Jog your memory on the essentials as you work and get clear answers to your questions Perfect for supplementing classroom learning, reviewing for a certification, and staying knowledgeable on the job, SQL Essentials For Dummies is the convenient, direct, and digestible reference you've been looking for. Claim your complimentary eBook worth $10 for free, before the offer ends on June 17. How to get it Please ensure you read the terms and conditions to claim this offer. Complete and verifiable information is required in order to receive this free offer. If you have previously made use of these free offers, you will not need to re-register. While supplies last! Download the SQL Essentials For Dummies eBook (worth $10) for free Offered by Wiley, view other free resources The below offers are also available for free for a limited time: Winxvideo AI V3.0 Lifetime License for PC ($69.95 Value) FREE – Expires 6/8 Aiarty Image Enhancer for PC/Mac ($85 Value) FREE – Expires 6/8 Solutions Architect's Handbook, Third Edition ($42.99 Value) FREE – Expires 6/10 AI and Innovation ($21 Value) FREE – Expires 6/11 Unruly: Fighting Back when Politics, AI, and Law Upend [...] ($18 Value) FREE - Expires 6/17 SQL Essentials For Dummies ($10 Value) FREE – Expires 6/17 Continuous Testing, Quality, Security, and Feedback ($27.99 Value) FREE – Expires 6/18 VideoProc Converter AI v7.5 for FREE (worth $78.90) – Expires 6/18 Macxvideo AI ($39.95 Value) Free for a Limited Time – Expires 6/22 The Ultimate Linux Newbie Guide – Featured Free content Python Notes for Professionals – Featured Free content Learn Linux in 5 Days – Featured Free content Quick Reference Guide for Cybersecurity – Featured Free content We post these because we earn commission on each lead so as not to rely solely on advertising, which many of our readers block. It all helps toward paying staff reporters, servers and hosting costs. Other ways to support Neowin The above deal not doing it for you, but still want to help? Check out the links below. Check out our partner software in the Neowin Store Buy a T-shirt at Neowin's Threadsquad Subscribe to Neowin - for $14 a year, or $28 a year for an ad-free experience Disclosure: An account at Neowin Deals is required to participate in any deals powered by our affiliate, StackCommerce. For a full description of StackCommerce's privacy guidelines, go here. Neowin benefits from shared revenue of each sale made through the branded deals site.
    • No one is surprised. It goes both ways. Warhammer remake is not on the PS5, but Xbox/PC and Game Pass day 1 and it is NOT a Microsoft owned game (Sega). 100% Microsoft money deal. Timed of course. Sega wins because they get the Microsoft money now then when the deal ends they get the much larger PS audience to make money from. As far as Black Myth is concerned, the Chinese gaming platform market is very much PC then PS then everything else. Consoles were banned in China for years, then allowed and the PS is the console of choice. The release of Black Myth actually saw a PS5 console sale spike because of Chinese gamer's buying PS5's just to play this game. With the ever shrinking Xbox console market I can see a point where 3rd party games try to get Sony exclusive money because the ROI for Xbox console versions of those games is a shrinking endeavor.
    • Yeah Microsoft's "recommendation" is based on some promotional partnership with Intel. This is not very news worthy.
    • These giant companies want to ensure a system where there is no legal means storing the content on your own device. That's practically the case already. There are so many ways which we are being screwed by the top 1%, and this is just a drop in the bucket.
  • Recent Achievements

    • One Year In
      Frinco90 earned a badge
      One Year In
    • Apprentice
      Frinco90 went up a rank
      Apprentice
    • Week One Done
      theevergreentree earned a badge
      Week One Done
    • Dedicated
      Fryer Tuck earned a badge
      Dedicated
    • Week One Done
      luxoxfurniture earned a badge
      Week One Done
  • Popular Contributors

    1. 1
      +primortal
      452
    2. 2
      +FloatingFatMan
      248
    3. 3
      snowy owl
      232
    4. 4
      ATLien_0
      207
    5. 5
      Xenon
      146
  • Tell a friend

    Love Neowin? Tell a friend!